import akshare as ak
import pandas as pd
import time
import datetime
from dateutil.relativedelta import relativedelta
import json
import os, sys
from sqlalchemy import create_engine
from sqlalchemy import text

dir_path = os.path.dirname(os.path.abspath(__file__))
sys.path.append(os.path.join(dir_path, '..'))

from common import utils

def create_mysql_engine():
    db = 'shares'
    return create_engine(
        'mysql+pymysql://{0}:{1}@{2}:{3}/{4}?charset=utf8'.format('root', 'XXyy%402023', 'localhost', '3306', db))

# 创建本地缓存目录
if not os.path.exists("cache"):
    os.mkdir("cache")

jlr = None
jlr_path = '/usr/share/nginx/html/888/lrb.json'
if os.path.exists(jlr_path):
    with open(jlr_path, 'r') as f:
        jlr = json.load(f)

allStockInfo = {}
stock_info_path = os.path.join(dir_path, 'stock_info.json')
if os.path.exists(stock_info_path):
    with open(stock_info_path, 'r') as f:
        allStockInfo = json.load(f)
        print("load stock info ", len(allStockInfo), " from ", stock_info_path)

def updateStockInfo(code) :
    print("update stock info for ", code)
    df = ak.stock_individual_info_em(symbol=code)
    item = {}
    for index, row in df.iterrows():
        item[row['item']] = row['value']

    allStockInfo[code] = item
    print("done")
    with open(stock_info_path, 'w') as f:
        json.dump(allStockInfo, f, ensure_ascii=False)

def find(force = False, test = False):
    begin = datetime.date.today() - relativedelta(months=+6)
    begin_1 = datetime.date.today() - relativedelta(months=+1)
    begin_1y = datetime.date.today() - relativedelta(years=+1)
    end = time.strftime("%Y%m%d", time.localtime(time.time()))

#    stock_zh_a_hist_df = ak.stock_zh_a_hist(symbol="000001", period="daily", start_date=end, end_date=end, adjust="")
#    if (stock_zh_a_hist_df.shape[0] == 0) and not force:
#        print("no trade")
#        return

    weekday = datetime.datetime.now().weekday()
    if (weekday == 5 or weekday == 6) and not force:
        print("weekday %d" % (weekday))
        return

    # 首先加载所有的股票代码，大概7000支
    if os.path.exists("cache/all_stocks.csv"):
        df = pd.read_csv("cache/all_stocks.csv", dtype={'代码':str})
    else:
        df = ak.stock_zh_a_spot_em()
        df.to_csv("cache/all_stocks.csv")

    engine = create_mysql_engine()
    con = engine.connect()

    count = 2 # 要求最小值出现的次数

    path = '/usr/share/nginx/html/888/ret.json'
    path_t = '/usr/share/nginx/html/888/ret_t.json'
    history = {}
    todayResult = {}
    newResult = {}
    if os.path.exists(path):
        with open(path, 'r') as f:
            result = json.load(f)
            dates = result['dates']
            if len(dates) > 10:
                for d in dates[-10:]:
                    history[d] = result['history'][d]
            else:
                history = result['history']

    for index, row in df.iterrows():
        code = row['代码']
        name = row['名称']
        # 排除特殊类别
        if utils.isBadStock(code, name):
            continue

        jlr_diff = '未知'
        if (jlr is not None and code in jlr):
            jlr_diff = jlr[code]
        else:
            continue

        table_name = 'stock_%s' % (code)
        has_table = engine.dialect.has_table(con, table_name)
        if not has_table:
            continue

        rs = con.execute(text('select max(收盘) from (select * from %s WHERE 日期>=\'%s\' and 日期<=\'%s\') tb' % (table_name, begin, end)))
        if (rs.rowcount <= 0):
            continue
        row = rs.first()
        highestVal = row[0]

        rs = con.execute(text('select max(收盘) from (select * from %s WHERE 日期>=\'%s\' and 日期<=\'%s\') tb' % (table_name, begin_1, end)))
        if (rs.rowcount <= 0):
            continue
        row = rs.first()
        highValLastMonth = row[0]

        rs = con.execute(text('select max(收盘) from (select * from %s WHERE 日期>=\'%s\' and 日期<=\'%s\') tb' % (table_name, begin_1y, end)))
        if (rs.rowcount <= 0):
            continue
        row = rs.first()
        highValLastYear = row[0]

        rs = con.execute(text('select 最低,日期 from %s WHERE 日期>=\'%s\' and 日期<=\'%s\' order by 最低 limit 6' % (table_name, begin, end)))
        row = rs.fetchall()

        order = 0
        lowestVal = 0
        lowestDates = []
        lowestDateStr = ""
        for item in row:
            if order == 0:
                lowestVal = item[0]
                lowestDates.append(str(item[1]))
                order+=1
                lowestDateStr += "%s (%.2f)" % (str(item[1]), lowestVal)
#            elif (item[0] - lowestVal)/lowestVal <= 0.005:
            elif item[0] - lowestVal <= 0.01:
                lowestDates.append(str(item[1]))
                order+=1
                if (order <= 2):
                    lowestDateStr += ", %s (%.2f)" % (str(item[1]), item[0])
            else:
                break

        highestDate = con.execute(text('select 日期 from %s WHERE 收盘=%.2f and 日期>=\'%s\' and 日期<=\'%s\' order by 日期' % (table_name, highestVal, begin, end)))
        if highestDate.rowcount < 1:
            continue

        rs = con.execute(text('select 日期,开盘,收盘,换手率,涨跌幅 from %s WHERE 日期>=\'%s\' and 日期<=\'%s\' order by 日期 desc limit 1' % (table_name, begin, end)))
        row = rs.first()
        latestDate = row[0]
        latestOpen = row[1]
        latestClose = row[2]
        latestHsl = row[3]
        latestUp = row[4]

        cur_min = (latestClose - lowestVal) * 100 / (highestVal - lowestVal)
        cur_min_1 = (latestClose - lowestVal) * 100 / (highValLastMonth - lowestVal)
        cur_min_1y = (latestClose - lowestVal) * 100 / (highValLastYear - lowestVal)

        if order < count:
            if not ('正式' in jlr_diff and cur_min_1y < 40):
                continue

        highestDates = []
        for date in highestDate:
            highestDates.append(str(date[0]))

        if latestUp > 0:
            up = 'UP %.2f%%' % (latestUp)
        elif latestUp < 0:
            up = 'DN %.2f%%' % (latestUp)
        else:
            up = 'SM %.2f%%' % (latestUp)

        todayResult[code] = {
            'code': code,
            'date': end,
            'jlr': jlr_diff,
            'low': lowestDateStr,
            'high': '年%.2f,半%.2f,月%.2f' % (highValLastYear, highestVal, highValLastMonth),
            'late': '换手%.2f,开%.2f,收%.2f. %s' % (latestHsl, latestOpen, latestClose, up),
            'lowDate': lowestDates,
            'highDate': highestDates,
            'lateDate': str(latestDate),
            'space': '年%.2f%%,半%.2f%%,月%.2f%%' % (100 - cur_min_1y, 100 - cur_min, 100 - cur_min_1),
        }
    #end loop

    history[end] = todayResult
    dates = list(history.keys())
    codes = set()

    for oneday in history.values():
        for code in oneday:
            codes.add(code)

    codes = list(codes)
    infos = {}
    for code in codes:
        if code not in allStockInfo:
            updateStockInfo(code)

        item = allStockInfo[code]
        infos[code] = {
            'zsz' : utils.formatNumber(item['总市值']),
            'ltsz' : utils.formatNumber(item['流通市值']),
            'cls' : item['行业'],
            'name' : item['股票简称'],
        }


    codes.sort()
    dates.sort()

    result = {
        'codes': codes,
        'infos': infos,
        'dates': dates,
        'history': history
    }

    if test:
        path = path_t

    with open(path, 'w') as f:
        json.dump(result, f, ensure_ascii=False)

if __name__=='__main__':
    find(len(sys.argv) > 1, len(sys.argv) > 2)

